/*
 * Created on Apr 9, 2004
 */
package com.scalar.chiptrack.operations.process.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.PMFProcessInfo;
import com.scalar.chiptrack.operations.process.ProcessTypes;
import com.scalar.chiptrack.utils.SqlQueryMappings;

/**
 * @author Ravikanth
**/
public class PMFileDAO
{
    /**
     * @param productId
     * @return HashMap of FinishedGoods.
     */
    public static LinkedHashMap getFinishedGoodsMap( int productId ) throws ScalarException
    {
		LinkedHashMap finishedGoodsMap = new LinkedHashMap();
		Connection conn 			= null;
		PreparedStatement pstmt 	= null;
		ResultSet rs 				= null;

		try
		{
			conn 	= DataSource.getConnection();
			
			pstmt 	= conn.prepareStatement ( SqlQueryMappings.get( "operations.pmfile.getallfinishedgoods" ) );
			//pstmt 	= conn.prepareStatement ( "select product.processStep_Id from product, process_step where product.processStep_Id = process_step.processStep_Id and PS_Type_Name = 'Finished Goods' and P_Parent_Product_Id = ? " );
			pstmt.setInt( 1, productId );
			rs 		= pstmt.executeQuery();
			while ( rs.next() )
			{
				finishedGoodsMap.put( rs.getString( "processStep_Id" ), "" );
			}
		}
		catch ( Exception sqlex )
		{
			sqlex.printStackTrace();
			throw new ScalarException ( "Could not fetch the FinishedGoodsMap from database in PMFileDAO", sqlex );
		}
		finally
		{
			try
			{
				if (rs != null){ rs.close(); }
				if (pstmt != null){	pstmt.close(); }
				if (conn != null){ conn.close(); }
			}
			catch ( SQLException excp )
			{
				System.out.println(" Sql exception occured in getFinishedGoodsMap method in PMFileDAO");
			}
		}
		return finishedGoodsMap;
    }

    /**
     * @param fgProcessStepId
     * @return HashMap containing the processes for the product
     */
    public static ArrayList getProcessesMap( String fgProcessStepId ) throws ScalarException
    {
        ArrayList processesMapList  = new ArrayList();
		LinkedHashMap processesMap 	= new LinkedHashMap();
		PMFProcessInfo processInfo	= null;
		Connection conn 			= null;
		PreparedStatement pstmt 	= null;
		ResultSet rs 				= null;
		try
		{
			conn 	= DataSource.getConnection();
            pstmt       = conn.prepareCall( SqlQueryMappings.get ( "operations.pmfile.getAllProcessesFlowsForFinishedGoods" ) );
			pstmt.setInt( 1, Integer.parseInt( fgProcessStepId ) );
			rs 		= pstmt.executeQuery();
            int pathNumber  = -1;
			while ( rs.next() )
			{
				processInfo	= new PMFProcessInfo();
                pathNumber  = rs.getInt ( "PATH_NUMBER" );
				processInfo.setProcessStep_Id( rs.getInt( "PROCESSSTEP_ID" ) );
				processInfo.setProcessName( rs.getString( "PS_Type" ) );
                processInfo.setBaseProcessName( rs.getString( "PS_Type_Name" ) );
				processInfo.setProductName( rs.getString( "Product_Name" ) );
                if ( pathNumber <= processesMapList.size() )
                {
                    processesMap    = ( LinkedHashMap )processesMapList.get ( pathNumber -1 );
                }
                else
                {
                    processesMap    = new LinkedHashMap();
                    processesMapList.add ( processesMap );
                }
				processesMap.put( processInfo, "" );
			}
		}
		catch ( Exception sqlex )
		{
			sqlex.printStackTrace();
			throw new ScalarException ( "Could not fetch the Processes Map from database in PMFileDAO", sqlex );
		}
		finally
		{
			try
			{
				if (rs != null){ rs.close(); }
				if (pstmt != null){	pstmt.close(); }
				if (conn != null){ conn.close(); }
			}
			catch ( SQLException excp )
			{
				System.out.println(" Sql exception occured in getProcessesMap method in PMFileDao");
			}
		}

        return processesMapList;
    }

    /**
     * @param processStepId
     * @return
     */
    public static LinkedHashMap getParameterMap( int processStepId ) throws ScalarException
    {
		LinkedHashMap parameterMap 	= new LinkedHashMap();
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rs 			= null;
		String vendorNames		= "";
		String partNumbers		= "";
        String psTypeName       = "";
        String basePartNumber   = "";

		try
		{
			conn 	= DataSource.getConnection();

            pstmt   = conn.prepareStatement( SqlQueryMappings.get ( "operations.pmfile.getBasePartNumberFromProcessStep" ) );
            pstmt.setInt( 1, processStepId );
            pstmt.setInt( 2, processStepId );
            rs      = pstmt.executeQuery();
            if ( rs.next () )
            {
                psTypeName      = rs.getString ( "PS_Type_Name" );
                basePartNumber  = rs.getString ( "Part_Number" );
            }

            rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

            //set base product name for fab process
            if ( ProcessTypes.fab.equalsIgnoreCase( psTypeName ) )
            {
                parameterMap.put( "Base Product", basePartNumber );
            }

			// set the Vendor Names
			pstmt	= conn.prepareStatement( SqlQueryMappings.get( "operations.pmfile.getVendorNamesByProcessStepID" ) );
			//pstmt	= conn.prepareStatement( "select V_Name from Vendor where Vendor_ID in ( select vendor_id from Location, Process_Step_Location_Table where  Location.Location_ID = Process_Step_Location_Table.Location_ID and processStep_Id = ? )" );
			pstmt.setInt( 1, processStepId );
			rs 		= pstmt.executeQuery();
			
			while( rs.next() )
			{
				vendorNames = vendorNames + rs.getString( "V_Name" ) + ",";
			}
			
			if( vendorNames != null && vendorNames.length() > 1 )
			{
				vendorNames = vendorNames.substring( 0, vendorNames.length() - 1 );
				parameterMap.put( "Vendor", vendorNames );
			}
			
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			// set the Vendor Part Numbers
			pstmt	= conn.prepareStatement( SqlQueryMappings.get( "operations.pmfile.getProductDuplicatePartNumbers" ) );
			//pstmt	= conn.prepareStatement( "select distinct Part_Number from Product_Map_Table where ProcessStep_Id = ?" );
			pstmt.setInt( 1, processStepId );
			rs 		= pstmt.executeQuery();
			
			while( rs.next() )
			{
				partNumbers = partNumbers + rs.getString( "Part_Number" ) + ",";
			}
			
			if( partNumbers != null && partNumbers.length() > 1 )
			{
				partNumbers = partNumbers.substring( 0, partNumbers.length() - 1 );
				parameterMap.put( "Vendor Part Numbers ", partNumbers );
			}
			
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

            //SET SPEC FILE NAME FOR ASSEMBLY, SORT AND TEST
            if ( ProcessTypes.assembly.equalsIgnoreCase( psTypeName ) || ProcessTypes.sort.equalsIgnoreCase( psTypeName ) || ProcessTypes.test.equalsIgnoreCase( psTypeName ) )
            {
                pstmt   = conn.prepareStatement( SqlQueryMappings.get( "operations.pmfile.getSpecFileNameByProcessStepId" ) );
                pstmt.setInt ( 1, processStepId );
                rs      = pstmt.executeQuery();
                String specFileName = "";
                if ( rs.next () )
                {
                    specFileName    = rs.getString ( "Spec_File_Name" );
                }
                parameterMap.put ( "Spec File Name", specFileName );

                rs.close();
                rs = null;
                pstmt.close();
                pstmt = null;
            }

            //SET THE BINNING INFO FOR ASSEMBLY PROCESS
            if ( ProcessTypes.assembly.equalsIgnoreCase( psTypeName ) )
            {
                pstmt   = conn.prepareStatement( SqlQueryMappings.get ( "operations.pmfile.getAssemblyGoodBins" ) );
                pstmt.setInt ( 1, processStepId );
                pstmt.setInt ( 2, processStepId );
                pstmt.setString ( 3, ProcessTypes.sort );

                rs      = pstmt.executeQuery();
                StringBuffer binNumbers = new StringBuffer ( "" );
                String temp = null;
                while ( rs.next () )
                {
                    temp    = rs.getString ( "Bin_Number" );
                    if ( temp != null )
                    {
                        binNumbers.append ( temp );
                        binNumbers.append ( "," );
                    }
                }
                if ( binNumbers.lastIndexOf(",") > 1 )
                {
                    binNumbers.deleteCharAt( binNumbers.lastIndexOf(",") );
                    if ( binNumbers.lastIndexOf(",") > 1 )
                    {
                        binNumbers.replace( binNumbers.lastIndexOf(","), binNumbers.lastIndexOf(",")+1, "&" );
                    }
                }
                parameterMap.put ( "Good Bins", binNumbers.toString() );

                rs.close();
                rs = null;
                pstmt.close();
                pstmt = null;
            }

			pstmt 	= conn.prepareCall( SqlQueryMappings.get( "operations.pmfile.getProcessParameters" ) );
			//pstmt 	= conn.prepareCall( "Select * from Process_Parameter where ProcessStep_ID = ?" );
			pstmt.setInt( 1, processStepId );
			rs 		= pstmt.executeQuery();
			while ( rs.next() )
			{
				parameterMap.put( rs.getString( "Parameter_Key" ), rs.getString( "Parameter_Value" ) );
			}
			
			// To be removed 
			//Iterator it = parameterMap.keySet().iterator();
			//while( it.hasNext() )
			//{
			//	System.out.println( " parameter  " + (String)it.next() ); 
			//}
			
		}
		catch ( Exception sqlex )
		{
			sqlex.printStackTrace();
			throw new ScalarException ( "Could not fetch the Parameter Map from database in PMFileDAO", sqlex );
		}
		finally
		{
			try
			{
				if (rs != null){ rs.close(); }
				if (pstmt != null){	pstmt.close(); }
				if (conn != null){ conn.close(); }
			}
			catch ( SQLException excp )
			{
				System.out.println(" Sql exception occured in getProcessesMap method in PMFileDao");
			}
		}
        return parameterMap;
    }

    /**
     * @param processStep_Id
     * @return
     */
    public static HashMap getFinishedGoodsInfo( int processStep_Id ) throws ScalarException
    {
		HashMap finishedGoodsInfo 	= new HashMap();
		Connection conn 			= null;
		PreparedStatement pstmt 	= null;
		ResultSet rs 				= null;
		
		try
		{
			conn 	= DataSource.getConnection();
			pstmt 	= conn.prepareCall( SqlQueryMappings.get( "operations.pmfile.getFinishedGoodsInfo" ) );
			//pstmt 	= conn.prepareCall( "select Part_Number, P_Comments, P_Marketing_Part_Number, IS_Approved from product where processStep_ID = ?" );
			pstmt.setInt( 1, processStep_Id );
			rs 		= pstmt.executeQuery();
			if ( rs.next() )
			{
				finishedGoodsInfo.put( "Part_Number", rs.getString( "Part_Number" ) );
				finishedGoodsInfo.put( "P_Comments", rs.getString( "P_Comments" ) );
				finishedGoodsInfo.put( "P_Marketing_Part_Number", rs.getString( "P_Marketing_Part_Number" ) );
				finishedGoodsInfo.put( "IS_Approved", rs.getString( "IS_Approved" ) );
			}
			
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			pstmt 	= conn.prepareCall( SqlQueryMappings.get( "operations.pmfile.getBaseProductName" ) );
			//pstmt 	= conn.prepareCall( "select Part_Number from product where product_Id = ( select P_Parent_Product_Id from product where processStep_ID = ?)" );
			pstmt.setInt( 1, processStep_Id );
			rs 		= pstmt.executeQuery();
			if ( rs.next() )
			{
				finishedGoodsInfo.put( "Product Family", rs.getString( "Part_Number" ) );
			}
		}
		catch ( Exception sqlex )
		{
			sqlex.printStackTrace();
			throw new ScalarException ( "Could not fetch the Processes Map from database in PMFileDAO", sqlex );
		}
		finally
		{
			try
			{
				if (rs != null){ rs.close(); }
				if (pstmt != null){	pstmt.close(); }
				if (conn != null){ conn.close(); }
			}
			catch ( SQLException excp )
			{
				System.out.println(" Sql exception occured in getProcessesMap method in PMFileDao");
			}
		}
		
        return finishedGoodsInfo;
    }

    /**
     * @param productId
     * @return
     
    public static String getParentProductName( int productId ) throws ScalarException
    {
		String parentProductName	= null;
		Connection conn 			= null;
		PreparedStatement pstmt 	= null;
		ResultSet rs 				= null;
		
		try
		{
			conn 	= DataSource.getConnection();
			pstmt 	= conn.prepareCall( "select Part_Number from product where product_Id = ?" );
			pstmt.setInt( 1, productId );
			rs 		= pstmt.executeQuery();
			if ( rs.next() )
			{
				parentProductName = rs.getString( "Part_Number" );
			}
		}
		catch ( Exception sqlex )
		{
			sqlex.printStackTrace();
			throw new ScalarException ( "Could not fetch the getParentProductName from database in PMFileDAO", sqlex );
		}
		finally
		{
			try
			{
				if (rs != null){ rs.close(); }
				if (pstmt != null){	pstmt.close(); }
				if (conn != null){ conn.close(); }
			}
			catch ( SQLException excp )
			{
				System.out.println(" Sql exception occured in getParentProductName method in PMFileDao");
			}
		}
		
		return parentProductName;
    }*/
}